﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using OfficeOpenXml.Style;

public partial class admincp_nhapdiem : AdminPage
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Page_PreRender(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.FillDataToDropDownListNamHoc();
            this.FillDataToDropDownListHocKy();
            this.FillDataToDropDownListLopHoc();
            this.FillDataToDropDownListMonHoc();
        }
    }

    private void FillDataToDropDownListNamHoc()
    {
        ddlNamHoc.DataSource = db.NamHocs.Where(nh => nh.TrangThai == true);
        ddlNamHoc.DataTextField = "TenNamHoc";
        ddlNamHoc.DataValueField = "MaNamHoc";
        ddlNamHoc.DataBind();
        ddlNamHoc.Items.Insert(0, "(Xin vui lòng chọn năm học)");
    }

    private void FillDataToDropDownListHocKy()
    {
        ddlHocKy.Items.Clear();
        ddlHocKy.Items.Insert(0, "(Xin vui lòng chọn học kỳ)");
        ddlHocKy.Items.Insert(1, "Học kỳ I");
        ddlHocKy.Items.Insert(2, "Học kỳ II");
    }

    private void FillDataToDropDownListLopHoc()
    {
        var lophoc = (from plgv in db.PhanLopGiaoViens
                      where plgv.MaGiaoVien == this.Employee.MaGiaoVien && plgv.LopHoc.MaNamHoc == ddlNamHoc.SelectedValue
                      select new
                      {
                          MaLop = plgv.LopHoc.MaLop,
                          TenLop = plgv.LopHoc.TenLop
                      }).Distinct();
        ddlLopHoc.DataSource = lophoc;
        ddlLopHoc.DataTextField = "TenLop";
        ddlLopHoc.DataValueField = "MaLop";
        ddlLopHoc.DataBind();
        ddlLopHoc.Items.Insert(0, "(Xin vui lòng chọn lớp)");
    }

    private void FillDataToDropDownListMonHoc()
    {
        var monhoc = from plgv in db.PhanLopGiaoViens
                     where plgv.MaGiaoVien == this.Employee.MaGiaoVien && plgv.LopHoc.MaLop == ddlLopHoc.SelectedValue
                     select new
                     {
                         TenMonHoc = plgv.MonHoc.TenMonHoc,
                         MaMonHoc = plgv.MonHoc.MaMonHoc
                     };
        ddlMonHoc.DataSource = monhoc;
        ddlMonHoc.DataTextField = "TenMonHoc";
        ddlMonHoc.DataValueField = "MaMonHoc";
        ddlMonHoc.DataBind();
        ddlMonHoc.Items.Insert(0, "(Xin vui lòng chọn môn dạy)");
    }

    private void FillDataToGridView()
    {
        var items = (from phanlop in db.PhanLopHocSinhs
                     where phanlop.MaLop == ddlLopHoc.SelectedValue
                     select new
                     {
                         SoThuTu = phanlop.SoThuTu,
                         MaHocSinh = phanlop.MaHocSinh,
                         SoPhuHieu = phanlop.SoPhuHieu,
                         TenHocSinh = phanlop.HocSinh.TenHocSinh,
                         Mieng = "",
                         d15p1 = "",
                         d15p2 = "",
                         d15p3 = "",
                         d1t1 = "",
                         d1t2 = "",
                         d1t3 = "",
                         dhk = ""
                     }).OrderBy(pl => pl.SoThuTu);
        lvNhapDiem.DataSource = items;
        lvNhapDiem.DataBind();

        foreach (ListViewItem lvi in lvNhapDiem.Items)
        {
            HiddenField hdfMaHocSinh = lvi.FindControl("hdfMaHocSinh") as HiddenField;
            TextBox txtDiemMieng = lvi.FindControl("txtDiemMieng") as TextBox;
            TextBox txtDiem15p1 = lvi.FindControl("txtDiem15p1") as TextBox;
            TextBox txtDiem15p2 = lvi.FindControl("txtDiem15p2") as TextBox;
            TextBox txtDiem15p3 = lvi.FindControl("txtDiem15p3") as TextBox;
            TextBox txtDiem1T1 = lvi.FindControl("txtDiem1T1") as TextBox;
            TextBox txtDiem1T2 = lvi.FindControl("txtDiem1T2") as TextBox;
            TextBox txtDiem1T3 = lvi.FindControl("txtDiem1T3") as TextBox;
            TextBox txtDiemHKy = lvi.FindControl("txtDiemHKy") as TextBox;
            try
            {
                BangDiem bangdiem = db.BangDiems.Single(bd => bd.MaGiaoVien == this.Employee.MaGiaoVien &&
                    bd.MaHocSinh == long.Parse(hdfMaHocSinh.Value) &&
                    bd.MaLop == ddlLopHoc.SelectedValue && bd.MaMonHoc == ddlMonHoc.SelectedValue &&
                    bd.HocKy == Byte.Parse(ddlHocKy.SelectedIndex.ToString()));
                txtDiemMieng.Text = bangdiem.DM_1.ToString();
                txtDiem15p1.Text = bangdiem.D15_1.ToString();
                txtDiem15p2.Text = bangdiem.D15_2.ToString();
                txtDiem15p3.Text = bangdiem.D15_3.ToString();
                txtDiem1T1.Text = bangdiem.D1T_1.ToString();
                txtDiem1T2.Text = bangdiem.D1T_2.ToString();
                txtDiem1T3.Text = bangdiem.D1T_3.ToString();
                txtDiemHKy.Text = bangdiem.DThi.ToString();
            }
            catch
            {
                txtDiemMieng.Text = "";
                txtDiem15p1.Text = "";
                txtDiem15p2.Text = "";
                txtDiem15p3.Text = "";
                txtDiem1T1.Text = "";
                txtDiem1T2.Text = "";
                txtDiem1T3.Text = "";
                txtDiemHKy.Text = "";
            }
        }
    }
    protected void ddlNamHoc_SelectedIndexChanged(object sender, EventArgs e)
    {
        this.FillDataToDropDownListHocKy();
        this.FillDataToDropDownListLopHoc();
        this.FillDataToDropDownListMonHoc();
    }
    protected void ddlLopHoc_SelectedIndexChanged(object sender, EventArgs e)
    {
        this.FillDataToDropDownListMonHoc();
    }

    protected void XacNhanBatDauNhapDiem(object sender, EventArgs e)
    {
        try
        {
            if (ddlLopHoc.SelectedIndex < 1 || ddlMonHoc.SelectedIndex < 1 || ddlNamHoc.SelectedIndex < 1 || ddlHocKy.SelectedIndex < 1)
            {
                ClientScriptManager script = Page.ClientScript;
                if (!script.IsClientScriptBlockRegistered(this.GetType(), "Alert"))
                    script.RegisterClientScriptBlock(this.GetType(), "Alert", "<script type=text/javascript>alert('Xin vui lòng chọn đầy đủ thông tin')</script>");
            }
            else
            {
                this.FillDataToGridView();
            }
        }
        catch
        {
        }
    }

    protected void LuuNhapDiem(object sender, EventArgs e)
    {
        try
        {
            if (ddlLopHoc.SelectedIndex < 1 || ddlMonHoc.SelectedIndex < 1 || ddlNamHoc.SelectedIndex < 1 || ddlHocKy.SelectedIndex < 1)
            {
                ClientScriptManager script = Page.ClientScript;
                if (!script.IsClientScriptBlockRegistered(this.GetType(), "Alert"))
                    script.RegisterClientScriptBlock(this.GetType(), "Alert", "<script type=text/javascript>alert('Xin vui lòng chọn đầy đủ thông tin')</script>");
            }
            else
            {
                //TRƯỚC KHI TIẾN HÀNH LƯU CẦN XÓA HẾT BẢNG ĐIỂM CỦA CÁC HỌC SINH THUỘC LỚP VÀ MÔN HỌC ĐÃ CHỌN
                db.BangDiems.DeleteAllOnSubmit(db.BangDiems.Where(bd => bd.MaLop == ddlLopHoc.SelectedValue &&
                    bd.MaMonHoc == ddlMonHoc.SelectedValue && bd.MaGiaoVien == this.Employee.MaGiaoVien &&
                    bd.HocKy == Byte.Parse(ddlHocKy.SelectedIndex.ToString())));
                db.SubmitChanges();
                //Tiến hành lưu lại vào bảng điểm
                foreach (ListViewItem lvi in lvNhapDiem.Items)
                {
                    HiddenField hdfMaHocSinh = lvi.FindControl("hdfMaHocSinh") as HiddenField;
                    TextBox txtDiemMieng = lvi.FindControl("txtDiemMieng") as TextBox;
                    TextBox txtDiem15p1 = lvi.FindControl("txtDiem15p1") as TextBox;
                    TextBox txtDiem15p2 = lvi.FindControl("txtDiem15p2") as TextBox;
                    TextBox txtDiem15p3 = lvi.FindControl("txtDiem15p3") as TextBox;
                    TextBox txtDiem1T1 = lvi.FindControl("txtDiem1T1") as TextBox;
                    TextBox txtDiem1T2 = lvi.FindControl("txtDiem1T2") as TextBox;
                    TextBox txtDiem1T3 = lvi.FindControl("txtDiem1T3") as TextBox;
                    TextBox txtDiemHKy = lvi.FindControl("txtDiemHKy") as TextBox;

                    BangDiem bangdiem = new BangDiem();
                    bangdiem.MaGiaoVien = this.Employee.MaGiaoVien;
                    bangdiem.MaHocSinh = long.Parse(hdfMaHocSinh.Value);
                    bangdiem.MaLop = ddlLopHoc.SelectedValue;
                    bangdiem.MaMonHoc = ddlMonHoc.SelectedValue;
                    bangdiem.HocKy = Byte.Parse(ddlHocKy.SelectedIndex.ToString());
                    if (!String.IsNullOrEmpty(txtDiemMieng.Text))
                        bangdiem.DM_1 = float.Parse(txtDiemMieng.Text.Replace(',', '.'));
                    if (!String.IsNullOrEmpty(txtDiem15p1.Text))
                        bangdiem.D15_1 = float.Parse(txtDiem15p1.Text.Replace(',', '.'));
                    if (!String.IsNullOrEmpty(txtDiem15p2.Text))
                        bangdiem.D15_2 = float.Parse(txtDiem15p2.Text.Replace(',', '.'));
                    if (!String.IsNullOrEmpty(txtDiem15p3.Text))
                        bangdiem.D15_3 = float.Parse(txtDiem15p3.Text.Replace(',', '.'));
                    if (!String.IsNullOrEmpty(txtDiem1T1.Text))
                        bangdiem.D1T_1 = float.Parse(txtDiem1T1.Text.Replace(',', '.'));
                    if (!String.IsNullOrEmpty(txtDiem1T2.Text))
                        bangdiem.D1T_2 = float.Parse(txtDiem1T2.Text.Replace(',', '.'));
                    if (!String.IsNullOrEmpty(txtDiem1T3.Text))
                        bangdiem.D1T_3 = float.Parse(txtDiem1T3.Text.Replace(',', '.'));
                    if (!String.IsNullOrEmpty(txtDiemHKy.Text))
                        bangdiem.DThi = float.Parse(txtDiemHKy.Text.Replace(',', '.'));
                    db.BangDiems.InsertOnSubmit(bangdiem);
                    db.SubmitChanges();
                }
            }
        }
        catch
        {
            //Chuyển sang trang báo lỗi
        }
    }

    protected void XuatExcel(object sender, EventArgs e)
    {

        if (ddlLopHoc.SelectedIndex < 1 || ddlMonHoc.SelectedIndex < 1 || ddlNamHoc.SelectedIndex < 1 || ddlHocKy.SelectedIndex < 1)
        {
            ClientScriptManager script = Page.ClientScript;
            if (!script.IsClientScriptBlockRegistered(this.GetType(), "Alert"))
                script.RegisterClientScriptBlock(this.GetType(), "Alert", "<script type=text/javascript>alert('Xin vui lòng chọn đầy đủ thông tin')</script>");
        }
        else
        {

            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet ws = package.Workbook.Worksheets.Add(ddlLopHoc.SelectedItem.Text);
                //Tiến hành xuất ra tập tin Excel

                /****************************************************************
                *      BẮT ĐẦU ĐỊNH DẠNG PHẦN HEADER CHO FILE EXCEL             *
                ****************************************************************/
                ws.Cells["A1"].Value = "Trường THPT Trường Chinh\r\n" + ddlHocKy.SelectedItem.Text + " - " + ddlNamHoc.SelectedItem.Text;
                ws.Cells["A1"].Style.Font.Bold = true;
                //ws.Cells["A1"].Style.WrapText = true;
                //ws.Cells["A1:E1"].Merge = true;
                ws.Cells["E1"].Value = "MÔN " + ddlMonHoc.SelectedItem.Text;
                ws.Cells["E1"].Style.Font.Bold = true;
                ws.Cells["E1"].Style.Font.Size = 16;
                //ws.Cells["A2:E2"].Merge = true;
                ws.Cells["A2"].Value = "STT";
                ws.Cells["A2"].Style.Font.Bold = true;
                ws.Cells["B2"].Value = "MSHS";
                ws.Cells["B2"].Style.Font.Bold = true;
                ws.Cells["C2"].Value = "HỌ VÀ TÊN HS";
                ws.Cells["C2"].Style.Font.Bold = true;
                ws.Cells["D2"].Formula = "COUNTA($D$3:$D$100)";
                ws.Cells["D2"].Style.Font.Bold = true;
                ws.Cells["E2"].Value = "Miệng";
                ws.Cells["E2"].Style.Font.Bold = true;
                ws.Cells["F2"].Value = "15ph";
                ws.Cells["F2"].Style.Font.Bold = true;
                ws.Cells["G2"].Value = "15ph";
                ws.Cells["G2"].Style.Font.Bold = true;
                ws.Cells["H2"].Value = "15ph";
                ws.Cells["H2"].Style.Font.Bold = true;
                ws.Cells["I2"].Value = "1t";
                ws.Cells["I2"].Style.Font.Bold = true;
                ws.Cells["J2"].Value = "1t";
                ws.Cells["J2"].Style.Font.Bold = true;
                ws.Cells["K2"].Value = "1t";
                ws.Cells["K2"].Style.Font.Bold = true;
                ws.Cells["L2"].Value = "HKỳ";
                ws.Cells["L2"].Style.Font.Bold = true;
                ws.Cells["M2"].Value = "TBM";
                ws.Cells["M2"].Style.Font.Bold = true;
                ws.Cells["A2:M2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                ws.Cells["A2:M2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                var borderA2M2 = ws.Cells["A2:M2"].Style.Border;
                borderA2M2.Bottom.Style = borderA2M2.Left.Style = borderA2M2.Right.Style = borderA2M2.Top.Style = ExcelBorderStyle.Thin;
                /****************************************************************
                *      KẾT THÚC ĐỊNH DẠNG PHẦN HEADER CHO FILE EXCEL           *
                ****************************************************************/

                int DongBatDau = 3;

                foreach (ListViewItem lvi in lvNhapDiem.Items)
                {
                    HiddenField hdfMaHocSinh = lvi.FindControl("hdfMaHocSinh") as HiddenField;
                    TextBox txtDiemMieng = lvi.FindControl("txtDiemMieng") as TextBox;
                    TextBox txtDiem15p1 = lvi.FindControl("txtDiem15p1") as TextBox;
                    TextBox txtDiem15p2 = lvi.FindControl("txtDiem15p2") as TextBox;
                    TextBox txtDiem15p3 = lvi.FindControl("txtDiem15p3") as TextBox;
                    TextBox txtDiem1T1 = lvi.FindControl("txtDiem1T1") as TextBox;
                    TextBox txtDiem1T2 = lvi.FindControl("txtDiem1T2") as TextBox;
                    TextBox txtDiem1T3 = lvi.FindControl("txtDiem1T3") as TextBox;
                    TextBox txtDiemHKy = lvi.FindControl("txtDiemHKy") as TextBox;
                    if (!String.IsNullOrEmpty(hdfMaHocSinh.Value))
                    {
                        PhanLopHocSinh hocsinh = db.PhanLopHocSinhs.Single(hs => hs.MaHocSinh == long.Parse(hdfMaHocSinh.Value) &&
                            hs.MaLop == ddlLopHoc.SelectedValue);
                        ws.Cells[DongBatDau, 1].Value = hocsinh.SoThuTu;
                        ws.Cells[DongBatDau, 2].Value = hocsinh.SoPhuHieu;
                        string[] HovaTenHS = hocsinh.HocSinh.TenHocSinh.Split(' ');
                        string HoHS = hocsinh.HocSinh.TenHocSinh.Substring(0, hocsinh.HocSinh.TenHocSinh.LastIndexOf(' '));
                        string TenHS = hocsinh.HocSinh.TenHocSinh.Substring(hocsinh.HocSinh.TenHocSinh.LastIndexOf(' '));
                        ws.Cells[DongBatDau, 3].Value = HoHS;
                        ws.Cells[DongBatDau, 4].Value = TenHS;
                        if (!String.IsNullOrEmpty(txtDiemMieng.Text))
                            ws.Cells[DongBatDau, 5].Value = float.Parse(txtDiemMieng.Text);
                        if (!String.IsNullOrEmpty(txtDiem15p1.Text))
                            ws.Cells[DongBatDau, 6].Value = float.Parse(txtDiem15p1.Text);
                        if (!String.IsNullOrEmpty(txtDiem15p2.Text))
                            ws.Cells[DongBatDau, 7].Value = float.Parse(txtDiem15p2.Text);
                        if (!String.IsNullOrEmpty(txtDiem15p3.Text))
                            ws.Cells[DongBatDau, 8].Value = float.Parse(txtDiem15p3.Text);
                        if (!String.IsNullOrEmpty(txtDiem1T1.Text))
                            ws.Cells[DongBatDau, 9].Value = float.Parse(txtDiem1T1.Text);
                        if (!String.IsNullOrEmpty(txtDiem1T2.Text))
                            ws.Cells[DongBatDau, 10].Value = float.Parse(txtDiem1T2.Text);
                        if (!String.IsNullOrEmpty(txtDiem1T3.Text))
                            ws.Cells[DongBatDau, 11].Value = float.Parse(txtDiem1T3.Text);
                        if (!String.IsNullOrEmpty(txtDiemHKy.Text))
                            ws.Cells[DongBatDau, 12].Value = float.Parse(txtDiemHKy.Text);

                        var border = ws.Cells[DongBatDau, 1, DongBatDau, 13].Style.Border;
                        border.Bottom.Style = border.Left.Style = border.Right.Style = border.Top.Style = ExcelBorderStyle.Dotted;
                        DongBatDau++;
                    }
                }
                /****************************************************************
                *                             XUẤT RA EXCEL                     *
                ****************************************************************/
                Response.Clear();
                Response.BinaryWrite(package.GetAsByteArray());
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=nhapdiem.xlsx");
                Response.End();
                //package.SaveAs(Response.OutputStream);
                //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                //Response.AddHeader("content-disposition", "attachment;  filename=nhapdiem.xlsx");

            }
        }

    }
}